cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data"


///////////////////////////////////////////////////////////
// First, get the firm list


// process mining firms data
import excel using "header\miningfirmyears.xlsx", clear firstrow  allstring

keep if MTRVF=="1"
keep prodyr ultimate_parent parent_id bcoid n_mines n_mines_nonmtr prod_tons_nonmtr gvkey prod_tons
destring prodyr  bcoid n_mines n_mines_nonmtr prod_tons_nonmtr gvkey prod_tons, force replace
compress
gsort bcoid prodyr - prod_tons
rename prodyr year
bysort bcoid year: keep if _n==1
save mtr_year_producers, replace



///////////////////////////////////////////////////////////
// Second, get the bank data in order
import excel using "header\bank80policies-JOE-4-5-2022.xlsx", clear firstrow  allstring

rename *, lower

keep ultimateparentid enhancedreview projectban companyban minpolicyyear lender
destring ultimateparentid, force replace
bysort ultimateparentid: keep if _n==1
bysort lender: keep if _n==1
save lender_policies, replace


/////////////////////////////////////////////////////////////////
// prep bank IDs
use "Dealscan-Legacy-5-23-2022\company", clear
bysort companyid: keep if _n==1
rename country lender_country
keep companyid  ultimateparentid lender_country
save companyid-ultimateparentid, replace



////////////////////////////////////////////////////////////////////
// Combine legacy dealscan data

use "Dealscan-Legacy-5-23-2022\facility", clear
keep packageid facilityid
bysort facilityid: keep if _n==1
save packageid-facilityid-link, replace

use "Dealscan-Legacy-5-23-2022\lendershares", clear

bysort facilityid lender: keep if _n==1
drop lender
bysort facilityid companyid: keep if _n==1


// add ultimateparentid
joinby companyid using companyid-ultimateparentid, unm(master)
tab _m
keep if _m==3
drop _m

// first add packageid
sort facilityid
joinby facilityid using packageid-facilityid-link, unm(both)
tab _m
keep if _m==3
drop _m

// next, go to our banks
bysort ultimateparentid facilityid: keep if _n==1
joinby ultimateparentid using lender_policies, unm(both)
tab _m
keep if _m==3
drop _m

gen lead=0
replace lead=1 if regexm(lower(lenderrole),"lead arranger")

bysort packageid ultimateparentid: egen lead_arranger=max(lead)

gsort packageid ultimateparentid facilityid 
bysort packageid ultimateparentid: keep if _n==1

keep packageid lead_arranger  ultimateparentid enhancedreview projectban companyban minpolicyyear lender lender_country
save package-lender, replace


///////////////////////////////////////////////////////////
// prep company details 
use "Dealscan-Legacy-5-23-2022\company", clear
bysort companyid: keep if _n==1
rename companyid borrowercompanyid
keep borrowercompanyid primarysiccode
save companyid-sic, replace



// Load package
use "Dealscan-Legacy-5-23-2022\package", clear
bysort packageid: keep if _n==1

gen year=year(dealactivedate)
keep if year<=2020

// add company sic codes
sort borrowercompanyid
joinby borrowercompanyid using companyid-sic, unm(master)
tab _m
keep if _m==1 | _m==3
drop _m


// add our company production years
rename borrowercompanyid bcoid
sort bcoid year
joinby bcoid year using mtr_year_producers, unm(master)
tab _m
keep if _m==1 | _m==3
drop _m

// keep vars I need
keep packageid bcoid company year   dealactivedate dealamount currency exchangerate dealpurpose dealstatus  n_mines prod_tons n_mines_nonmtr prod_tons_nonmtr gvkey primarysiccode

// now, expand to all of our banks for each package
bysort packageid: keep if _n==1
joinby packageid using package-lender, unm(using)
tab _m
drop _m
bysort packageid ultimateparentid: keep if _n==1


keep if dealstatus=="Closed" | dealstatus=="Completed"
drop dealstatus

gen deal_amount_converted=dealamount*exchangerate
drop exchangerate currency
compress


// That's my unit of observation for deal-lender
bysort packageid ultimateparentid: keep if _n==1

count
/////////////////////////////////////////////
// Create variables for relationships
egen bank_company_id=group(ultimateparentid bcoid)
bysort bank_company_id: gen n=_n
tsset  bank_company_id n
gen deals10=0
gen deals5=0
forvalues i = 1/433 {
	replace deals10=deals10+1 if L`i'.year<year & L`i'.year>=year-10
    replace deals5=deals5+1 if L`i'.year<year & L`i'.year>=year-5
}

count

// That's my unit of observation for deal-lender
bysort packageid ultimateparentid: keep if _n==1

// add package details
joinby packageid using packageid-chars, unm(master)
tab _m
drop _m

drop n

compress
bysort packageid ultimateparentid: keep if _n==1
save lenders_borrowers_dataset, replace


